<?php
/**
* Classe permettant de manipuler les bases de données
* Classe utilisant le design patern singleton 
* Accés depuis l'exterieur via la methode statique database::getInstance()
* 
*/
class Database {

	private static $connection = null;

	/**
	* @desc : Constructeur, initialise la connection
	* @return void
	*/
	private function __construct()
	{
		try {
			// On instancie PDO et on se connecte a la base de données
			self::$connection = new PDO('mysql:host=localhost;dbname=m2l', 'root', '',  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
		} catch(PDOException $e) {
			echo "Erreur de connection à la base de données : <b>".$e->getMessage()."</b>";
		}
	}

	/**
	* @desc : Methode statique d'accès à la database, retourne une instance de la connection si elle existe, en créer une si aucune existante
	* @return instance de database
	*/
	public static function getInstance() {
		if(self::$connection === null) {
			return new self();
		}
		return self::$connection;
	}

	// -------------------------------------------------------
	// *** Début des methodes en rapport avec le materiel ***
	// -------------------------------------------------------
	public function addMateriel($designation, $idType, $idSousType, $stock) {

		$query = self::$connection->prepare("INSERT INTO materiel(designation, idType, idSousType, stock) VALUES(:designation, :idType, :idSousType, :stock)");
		$query->bindValue(":designation", $designation, PDO::PARAM_STR);
		$query->bindValue(":idType", $idType, PDO::PARAM_INT);
		$query->bindValue(":idSousType", $idSousType, PDO::PARAM_INT);
		$query->bindValue(":stock", $stock, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}

		return false;
	}

	public function updateMateriel($idMateriel, $designation, $idType, $idSousType, $stock) {
		
		$query = self::$connection->prepare("UPDATE materiel SET designation = :designation, idType = :idType, idSousType = :idSousType, stock = :stock WHERE idMateriel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);
		$query->bindValue(":designation", $designation, PDO::PARAM_STR);
		$query->bindValue(":idType", $idType, PDO::PARAM_INT);
		$query->bindValue(":idSousType", $idSousType, PDO::PARAM_INT);
		$query->bindValue(":stock", $stock, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}

		return false;
	}

	public function deleteMateriel($idMateriel) {
		
		$query = self::$connection->prepare("DELETE FROM materiel WHERE stock = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}

		return false;
	}

	public function updateStock($idMateriel, $tailleLot, $action="add") {

		$query = self::$connection->prepare("SELECT stock FROM materiel_sport WHERE id_materiel_sport = :id_materiel_sport");
		$query->bindValue(":id_materiel_sport", $idMateriel, PDO::PARAM_INT);
		$query->execute();

		$total = $query->fetch();

		$query = self::$connection->prepare("UPDATE materiel_sport SET stock = :stock WHERE id_materiel_sport = :id_materiel_sport");
		$query->bindValue(":id_materiel_sport", $idMateriel, PDO::PARAM_INT);
		
		if($action = "add") {
			$query->bindValue(":stock", $total+$tailleLot, PDO::PARAM_INT);
		} else {
			if($total-$tailleLot < 0) {
				return false;
			} else {
				$query->bindValue(":stock", $total-$tailleLot, PDO::PARAM_INT);
			}
		}

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}

		return false;
	}

	public function getStock($idMateriel) {

		$query = self::$connection->prepare("SELECT stock FROM materiel WHERE idMateriel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);

	}

	public function getInfoMateriel() {
	
	}
	// -------------------------------------------------------
	// *** Fin des methodes en rapport avec le materiel ***
	// -------------------------------------------------------

	// -------------------------------------------------------
	// *** Début des méthodes en rapport avec les adhérent ***
	// -------------------------------------------------------

	public function getInfoAdherant($idAdherant) {

		$query = self::$connection->prepare("SELECT nom, prenom, mail, certificat, adresse, ville, cp FROM adherant WHERE id_adherant = :idAdherant");
		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);

	}

	public function checkLogin($login) {

		$query = self::$connection->prepare("SELECT count(*) FROM adherant WHERE login = :login");
		$query->bindValue(":login", $login, PDO::PARAM_STR);

		$query->execute();

		if($query->fetchColumn() == 1) {	
			return true;
		}

		return false;

	}

	public function checkPassword($id_adherant, $password) {

		$query = self::$connection->prepare("SELECT count(*) FROM adherant WHERE password = :password AND id_adherant = :id_adherant");
		$query->bindValue(":password", $password, PDO::PARAM_STR);
		$query->bindValue(":id_adherant", $id_adherant, PDO::PARAM_INT);

		$query->execute();

		if($query->fetchColumn() == 1) {	
			return true;
		}

		return false;

	}

	public function listReservationFromAdherant($idAdherant) {
		$query = self::$connection->prepare("SELECT * FROM reservation WHERE id_adherant = :id_adherant AND date >= now()");
		$query->bindValue(":id_adherant", $idAdherant, PDO::PARAM_INT);

		$query->execute();

		if($query->fetchColumn() > 0) {
			return $query->fetchAll(PDO::FETCH_OBJ);
		}
		else {
			return false;
		}
	}

	// -------------------------------------------------------
	// *** Fin des methode en rapport avec les adhérent ***
	// -------------------------------------------------------







	// -------------------------------------------------------
	// *** Début des methode en rapport avec les réservation ***
	// -------------------------------------------------------
	

	     	// création nouvelle reservation.
	public function newReservation($date, $materiel, $quantite, $idAdherent) {


		$query = self::$connection->prepare("INSERT INTO reservation(date) VALUES(now())");

		$query->execute();

		$id = $query-> lastInsertId();


	
		if($query->rowCount() == 1 ){
			return true;
		}
		return false;
			
		

	}



//----------------------------------------------------------------------------------------------------


			// permet de mettre à jour une reservation deja existante.

	public function updateReservation($idReservation, $date) {

		$query = self::$connection->prepare("UPDATE date SET reservation WHERE id_reservation = :id_reservation");
	
		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);
		$query->bindValue(":date", $date, PDO::PARAM_DATE);

		$query->execute();


		if($query->rowCount() == 1 ) {
			return true;
		}

		return false;

	}



//----------------------------------------------------------------------------------------------------


		//suppression d'une réservation.
	public function deleteReservation($idReservation) {

		$this->modifieDisponibiliteMateriel($idReservation, $etat=true);

		$query = self::$connection->prepare("DELETE FROM reservation WHERE id_reservation = :id_reservation");
		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() == 1) {
			return true;
		}

		return false;

	}

//----------------------------------------------------------------------------------------------------

		//modifie l'état d'utilisation du matériel.
	public function modifieDispoibiliteMateriel($idReservation,$etat){


		$query = sefl::connection->prepare("UPDATE etat_reservation SET materiel, materiel_reservation
											WHERE materiel.id_materiel = materiel_reservation.materiel_id_materiel
											AND id_reservation = :id_reservation ") ;
		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);
		$query->bindValue(":etat_reservation", $etat, PDO::PARAM_BOOL);

		$query ->execute();

		if($query->rowCount() > 0){
			return true;
		}
		return false;
	}



//----------------------------------------------------------------------------------------------------

		// récupère les info d'une réservation.

	public function getInfoReservation($idReservation) {

		$query = self::$connection->prepare("SELECT date,description,reference,quantite 
											 FROM materiel_reservation,materiel, reservation 
											 WHERE reservation.id_reservation = materiel_reservation.reservation_id_reservation 
											 AND materiel_reservation.materiel_id_materiel = materiel.id_materiel 
											 AND id_reservation = :id_reservation");

		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);

		$query->execute();

		if($query->fetchColumn() > 0){
			return $query->fetchAll(PDO::FETCH_OBJ);
		}
		return false;

	}



//----------------------------------------------------------------------------------------------------

		// récupère la listes des clubs/associations d'un adhérent.
	public function recuperationAssociationAdherent($idAdherant){

		$query = self::$connection->prepare("SELECT numero_association FROM association_has_adherent
											 WHERE adherent_numero_license= :idAdherant");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}



//----------------------------------------------------------------------------------------------------


		// permet de récupérer la liste du matériel disponible pour un adhérent selon ses clubs/associations.
	public function listeMaterielPourAdherent($idAdherant) {

		$listeAssociation = $this->recuperationAssociationAdherent();

		$query = self::$connection->prepare("SELECT materiel_sport.id_materiel_sport FROM association, materiel_sport 
											 WHERE materiel_sport.association_numero_association = association.numero_association
											 AND association.numero_association = :listeAssociation
											 AND materiel_sport.stock > 0
											 AND materiel.etat_reservation = false");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->bindValue(":listeAssociation", $listeAssociation->adherant_numero_license, PDO::PARAM_INT)
		$query->execute();

		if($query->fetchColumn() > 0){
			return $query->fetchAll(PDO::FETCH_OBJ);
		}
		return false;
	}






	// -------------------------------------------------------
	// *** Fin des methode en rapport avec les réservation ***
	// -------------------------------------------------------
?>